set hive.exec.dynamic.partition= true;
set hive.exec.dynamic.partition.mode= 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode= 200;
set hive.exec.max.dynamic.partitions=200 ;
insert overwrite table jms_dm.dm_start_rnk_month_detail_dt partition (dt)
select code                             -- 员工code
     , name                             --员工名字
     , agent_code                       --代理区code
     , agent_name                       --代理区名字
     ,round(month_score_sum,4) as month_score_sum   --当月总得分
     , round(month_service_quality_score,4) as month_service_quality_score      --月服务质量得分
     , round(month_trilateral_complaint_score,4) as month_trilateral_complaint_score -- 月三方投诉得分
     , round(month_sign_complaint_score,4) as month_sign_complaint_score       -- 月签收投诉得分
     , round(month_issignrate_score,4) as month_issignrate_score           --月时效签收准点率得分
     , round(month_delver_sign_score,4) as month_delver_sign_score          --月派签得分
     , round(month_taking_success_score,4) as month_taking_success_score       --月揽签得分
     , row_number()
        over (order by month_score_sum desc ,month_aging_sign_rate desc,month_deliver_sign_avg ,month_taking_success_rate desc )                         as country_rank    --代理区排名
     , row_number()
        over (partition by agent_code order by month_score_sum desc ,month_aging_sign_rate desc,month_deliver_sign_avg ,month_taking_success_rate desc ) as agent_rank    --全国排名
        ,'{{ execution_date | cst_ds }}' as date_time
        ,'{{ execution_date | cst_ds }}' as dt

from (
         select code                                                                                                                           -- 员工code
              , max(name)                                                                                  as name                             --员工名字
              , agent_code                                                                                                                     --代理区code
              , max(agent_name)                                                                            as agent_name                       --代理区名字
              , sum(day_service_quality_score)                                                             as month_service_quality_score      --月服务质量得分
              , sum(day_trilateral_complaint_score)                                                        as month_trilateral_complaint_score --月三方投诉得分
              , sum(day_sign_complaint_score)                                                              as month_sign_complaint_score       -- 月签收投诉得分
              , sum(day_issignrate_score)                                                                  as month_issignrate_score           --月时效签收准点率得分
              , sum(day_delver_sign_score)                                                                 as month_delver_sign_score          --月派签得分
              , sum(day_taking_success_score)                                                              as month_taking_success_score       --月揽签得分
              , (sum(day_service_quality_score) + sum(day_trilateral_complaint_score) + sum(day_sign_complaint_score) +
                 sum(day_issignrate_score) + sum(day_delver_sign_score) +
                 sum(day_taking_success_score))                                                            as month_score_sum
              , sum(need_sign_count)                                                                       as need_sign_count                  --月时效签收应签收量
              , sum(aging_sign_in_time_count)                                                              as aging_sign_in_time_count         --月时效签收及时签收量
              , sum(deliver_aging_sign_time)                                                               as deliver_aging_sign_time          -- 月派签时长汇总
              , sum(deliver_aging_sign_cnt)                                                                as deliver_aging_sign_cnt           --月派签量总单量
              , sum(taking_success_sum)                                                                    as taking_success_sum               -- 月揽收成功总量
              , sum(order_taking_fail_sum)                                                                 as order_taking_fail_sum            -- 月揽收失败总量
              , round(sum(aging_sign_in_time_count) / sum(need_sign_count), 4)                             as month_aging_sign_rate
              , round(sum(deliver_aging_sign_time) / sum(deliver_aging_sign_cnt),
                      4)                                                                                   as month_deliver_sign_avg
              , round(sum(taking_success_sum) / (sum(taking_success_sum) + sum(order_taking_fail_sum)),
                      4)                                                                                   as month_taking_success_rate
         from jms_dm.dm_start_rnk_day_detail_dt
         where dt = '{{ execution_date | cst_ds }}'
         group by agent_code, code
     ) tmp
     distribute by 10
;
